﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;



    class DBAccess
    {
        public static SqlCommand sqlcmd = null;
        public static SqlDataAdapter DataAdapter = null;

        /// <summary>
        /// 用于删除、插入、更新 操作
        /// 无sql参数 返回的操作状态  有错误或没有结果 返回 0
        /// </summary>
        /// <returns></returns>
        public static int GetState(string sql)
        {
            int rs = 0;
            using (sqlcmd=new SqlCommand(sql,Conndb.getconn()))
            {
                try
                {
                    rs = sqlcmd.ExecuteNonQuery();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception)
                {

                    return 0;
                }
               
            }
            return rs;
        }

    /// <summary>
    /// 用于删除、插入、更新 操作
    /// 带入sql参数 返回的操作状态   有错误或没有结果 返回 0
    /// </summary>
    /// <param name="sql">sql字符串</param>
    /// <param name="SqlPArry">参数化查询的参数</param>
    /// <returns></returns>
    public static int GetState(string sql, SqlParameter[] SqlPArry)
        {
            int rs = 0;
            using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
            {
            sqlcmd.Parameters.AddRange(SqlPArry);
            try
                {
                    
                    rs = sqlcmd.ExecuteNonQuery();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception)
                {

                    return 0;
                }

            }
            return rs;
            
        }
        /// <summary>
        /// 
        /// 返回 操作结果的第一个值（单值）
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>

        public static object GetOne(string sql)
        {
            object rs = null;
            using (sqlcmd=new SqlCommand(sql,Conndb.getconn()))
            {
                try
                {
                    rs = sqlcmd.ExecuteScalar();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception)
                {
                    return null;
                }
            }

            return rs;
        }

        /// <summary>
        /// 返回 操作结果的第一个值,带sql参数的
        /// 无结果，返回空
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="SqlPArry">参数绑定的数组</param>
        /// <returns></returns>
        public static object GetOne(string sql, SqlParameter[] SqlPArry)
        {
            object rs = null;
            using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
            {
                sqlcmd.Parameters.AddRange(SqlPArry);
                try
                {
                    rs = sqlcmd.ExecuteScalar();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception)
                {
                    return null;
                }
            }

            return rs;
        }


        /// <summary>
        /// 返回数据的DataReader 不带参数  
        ///  错误返回空！
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql)
        {
            SqlDataReader reDate = null;
            using (sqlcmd=new SqlCommand(sql,Conndb.getconn()))
            {
                try
                {
                    reDate = sqlcmd.ExecuteReader();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception)
                {

                         return null;
                }
            }
            
            return reDate;
        }



        /// <summary>
        /// 返回数据的DataReader，带查询参数
        /// 错误返回空！
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="SqlPArry">参数绑定的数组</param>
        /// <returns></returns>
        public static SqlDataReader GetReader(string sql, SqlParameter[] SqlPArry)
        {
            SqlDataReader reDate = null;
            using (sqlcmd = new SqlCommand(sql, Conndb.getconn()))
            {
                sqlcmd.Parameters.AddRange(SqlPArry);
                try
                {
                    reDate = sqlcmd.ExecuteReader();
                    sqlcmd.Parameters.Clear();
                }
                catch (Exception ex)
                {
                    return null;
                }
            }

            return reDate;
        }

        /// <summary>
        /// 获取数据根据sql语句 返回一个整张表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql)
        {
            DataTable dt = new DataTable();
            using (DataAdapter=new SqlDataAdapter(sql,Conndb.getconn()))
            {
                DataAdapter.Fill(dt);
                sqlcmd.Parameters.Clear();
                return dt;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="SqlPArry"></param>
        /// <returns></returns>
        public static DataTable GetTable(string sql, SqlParameter[] SqlPArry)
        {
            DataTable dt = new DataTable();
            using (DataAdapter = new SqlDataAdapter(sql, Conndb.getconn()))
            {
                DataAdapter.SelectCommand.Parameters.AddRange(SqlPArry);
                DataAdapter.Fill(dt);
                sqlcmd.Parameters.Clear();
                return dt;
            }
        }
        public static DataSet RunProcedure(string storedProcName,
            IDataParameter[] parameters, int Times = 30)
        {
            using (SqlConnection connection = Conndb.getconn())
            {
                DataSet dataSet = new DataSet();
                //connection.Open();
                SqlDataAdapter sqlDA = new SqlDataAdapter();
                sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);
                sqlDA.SelectCommand.CommandTimeout = Times;
                sqlDA.Fill(dataSet);
                connection.Close();
                return dataSet;
              
            }
        }
        public static int RunProcedureExecuteNonQuery(string storedProcName,
            IDataParameter[] parameters, int Times = 30)
        {
            using (SqlConnection connection = Conndb.getconn())
            {
                DataSet dataSet = new DataSet();
                //connection.Open();
                //SqlDataAdapter sqlDA = new SqlDataAdapter();
                SqlCommand cmd = BuildQueryCommand(connection, storedProcName, parameters);
                //sqlDA.SelectCommand.CommandTimeout = Times;
                //sqlDA.Fill(dataSet);
                //connection.Close();
                int res = cmd.ExecuteNonQuery();
                
                return res;

            }
        }
        /// <summary>
        /// 构建SqlCommand对象
        /// </summary>
        /// <param name="connection">数据库连接名</param>
        /// <param name="storeProcName"></param>
        /// <param name="parameters">存储过程参数</param>
        /// <returns></returns>
        private static SqlCommand BuildQueryCommand(SqlConnection connection, string storeProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storeProcName, connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null && parameters.Length > 0)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if (parameter != null)
                    {
                        if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) && (parameter.Value == null))
                        {
                            parameter.Value = DBNull.Value;
                        }
                        command.Parameters.Add(parameter);
                    }
                }
            }
            return command;
        }

    }


